﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using System.Data;
using System.Text.RegularExpressions;
namespace LogToDB
{
    class MysqlDB
    {
        //判断表是否存在
        public static bool IsTableExists(string tableName)
        {
            bool isExists = false;
           
            using (var conn =GetMysqlConnect())
            {
                string cmdText = "SHOW TABLES LIKE \'" + tableName + "\'";
                MySqlParameter[] commandParameter = new MySqlParameter[0];
                DataSet reader = MySqlHelper.GetDataSet(conn, CommandType.Text, cmdText, commandParameter);
                if (reader != null && reader.Tables.Count > 0 && reader.Tables[0].Rows.Count > 0)
                {
                    isExists = true;
                }
            }
            return isExists;
        }

        //检查表结构
        //MySql 获取表的字段名
        public static void CheckTableStructure<T>(string tableName)
        {         

            using (var conn = GetMysqlConnect())
            {
                string cmdText = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '"+ tableName + "'and table_schema = '"+ GetDBName()+ "'; ";
                MySqlParameter[] commandParameter = new MySqlParameter[0];             
                DataSet reader = MySqlHelper.GetDataSet(conn, CommandType.Text, cmdText, commandParameter);
                Dictionary<string,string> cloumn_name_dic = new Dictionary<string, string>();
                //MySql 获取表的字段名
                if (reader != null && reader.Tables.Count > 0)
                {
                    DataTable dt = reader.Tables[0];
                    
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        string cloumn_name = dr[0].ToString();
                        cloumn_name_dic.Add(cloumn_name, cloumn_name);
                    }
                }
                //检查类型中 定义的变量是否都在表中有对应的字段
                if (cloumn_name_dic.Count > 0) {
                    
                    System.Type vType = typeof(T);
                    System.Reflection.FieldInfo[] vFieldInfos = vType.GetFields();
                    foreach (System.Reflection.FieldInfo vFieldInfo in vFieldInfos)
                    {
                        string addName = vFieldInfo.Name;
                        if (!cloumn_name_dic.ContainsKey(addName)) {                           
                            string addType = Convert.ToString(vFieldInfo.FieldType);
                            string defaults = string.Empty;
                            if (addType.Contains("VARCHAR") || addType == "DateTime")
                            {
                                defaults = "default '0'";
                            }
                            else if(addType.Contains("Type"))//枚举类型
                            {
                                defaults = "default null";
                            }
                            else
                            {
                                defaults = "default 0";
                            }
                            cmdText = "alter table " + tableName + " add " + addName + " " + GetDBTypeByType(addType) + " " + defaults; 
                            CreateSQLData(conn, cmdText);
                        }
                    }
                }
                
            }
           
        }
        //获得 插入sql语句
        public static string GetDBMessage<T>(T inst, string tableName)
        {
            string cmdstr = "insert into " + tableName;
            string columnstr = " (";
            string valuestr = " (";
            cmdstr += ReflectionGetInsertStr<T>(columnstr, valuestr, inst);
            return cmdstr;
        }
        //获取添加DB数据的语句
        private static string ReflectionGetInsertStr<T>(string columnstr, string valuestr, T t)
        {
            string value = string.Empty;
            System.Type vType = typeof(T);
            System.Reflection.FieldInfo[] vFieldInfos = vType.GetFields();
            for (int i = 0; i < vFieldInfos.Length; i++)
            {
                System.Reflection.FieldInfo vFieldInfo = vFieldInfos[i];
                try
                {
                    string column = Convert.ToString(vFieldInfo.Name);
                    string colType = Convert.ToString(vFieldInfo.FieldType);
                    object colValue = vFieldInfo.GetValue(t);
                    colType = GetDBTypeByType(colType);
                    if (string.IsNullOrEmpty(colType))
                    {
                        continue;
                    }

                    ArrayConvertObject(Convert.ToString(vFieldInfo.FieldType), ref colValue);

                    columnstr += column + ",";
                    if (colType.Contains("VARCHAR") || colType == "DateTime")
                    {
                        if (colValue == null)
                            colValue = "";

                        valuestr += "'" + colValue + "'" + ",";
                    }
                    else
                    {
                        if (colValue == null)
                            colValue = "0";

                        ChangeSQLStringValue(ref colValue);
                        valuestr += colValue + ",";
                    }
                }
                catch (System.Exception e)
                {
                    Util.WriteLog(e.ToString());
                }
            }
            columnstr = columnstr.Remove(columnstr.LastIndexOf(','));
            valuestr = valuestr.Remove(valuestr.LastIndexOf(','));
            value += columnstr + ") values " + valuestr + ")";
            return value;
        }
        public static void ChangeSQLStringValue(ref object str)
        {
            if (Convert.ToString(str) == "True")
            {
                str = "1";
            }
            else if (Convert.ToString(str) == "False")
            {
                str = "0";
            }
        }

        public static string SetArray(int[] array)
        {
            string str = string.Empty;
            if (array != null)
            {
                for (int i = 0; i < array.Length; i++)
                {
                    str += Convert.ToString(array[i]) + "|";
                }
                if (str.LastIndexOf('|') >= 0)
                {
                    str = str.Remove(str.LastIndexOf('|'));
                }
            }

            return str;
        }

        public static string SetArray(float[] array)
        {
            string str = string.Empty;
            if (array != null)
            {
                for (int i = 0; i < array.Length; i++)
                {
                    str += Convert.ToString(array[i]) + "|";
                }
                if (str.LastIndexOf('|') >= 0)
                {
                    str = str.Remove(str.LastIndexOf('|'));
                }
                //str = str.Remove(str.LastIndexOf('|'));
            }

            return str;
        }
        public static string SetArray(List<int> array)
        {
            string str = string.Empty;
            if (array != null)
            {
                for (int i = 0; i < array.Count; i++)
                {
                    str += Convert.ToString(array[i]) + "|";
                }
                if (str.LastIndexOf('|') >= 0)
                {
                    str = str.Remove(str.LastIndexOf('|'));
                }
                //str = str.Remove(str.LastIndexOf('|'));
            }

            return str;
        }
        public static string SetArray(string[] array)
        {
            string str = string.Empty;
            if (array != null)
            {
                for (int i = 0; i < array.Length; i++)
                {
                    str += array[i] + "|";
                }
                if (str.LastIndexOf('|') >= 0)
                {
                    str = str.Remove(str.LastIndexOf('|'));
                }

                //str = str.Remove(str.LastIndexOf('|'));
            }
            return str;
        }
        public static void ArrayConvertObject(string arrayType, ref object colValue)
        {
            if (arrayType == "System.Single[]")
            {
                float[] array = (float[])colValue;
                colValue = SetArray(array);
            }
            else if (arrayType == "System.Int32[]")
            {
                int[] array = (int[])colValue;
                colValue = SetArray(array);
            }
            else if (arrayType == "System.String[]")
            {
                string[] array = (string[])colValue;
                colValue = SetArray(array);
            }
            else if (arrayType == "System.Collections.Generic.List`1[System.Int32]")
            {
                List<int> array = (List<int>)colValue;
                colValue = SetArray(array);
            }
        }
        //反射创建表
        public static void ReflectionCreateTable<T>(string tableName)
        {
            string CREATETABLE = "CREATE TABLE IF NOT EXISTS " + tableName + "(";
            System.Type vType = typeof(T);
            System.Reflection.FieldInfo[] vFieldInfos = vType.GetFields();
            T data = (T)System.Activator.CreateInstance(vType);
            for (int i = 0; i < vFieldInfos.Length; i++)
            {
                System.Reflection.FieldInfo vFieldInfo = vFieldInfos[i];
                try
                {
                    string fieldName = Convert.ToString(vFieldInfo.Name);
                    string fieldType = Convert.ToString(vFieldInfo.FieldType);
                    fieldType = GetDBTypeByType(fieldType);
                    string key = string.Empty;
                    string defaults = string.Empty;
                    bool allow_null = true;
                    string extra = "";
                    if (fieldName == "ID")
                    {
                        key = "PRI";
                        extra = "auto_increment";
                        allow_null = false;
                    }
                    

                    if (fieldType != string.Empty)
                    {
                        if (fieldType != "DateTime")
                        {
                            defaults = Convert.ToString(vFieldInfo.GetValue(data));
                            if (defaults == "System.Single[]" || defaults == "System.Int32[]" ||
                                defaults == "System.String[]" || defaults == "System.Collections.Generic.List`1[System.Int32]")
                            {
                                defaults = string.Empty;
                            }
                            else if (defaults == "True")
                            {
                                defaults = "1";
                            }
                            else if (defaults == "False")
                            {
                                defaults = "0";
                            }
                        }
                        if (fieldName.Contains("Type"))
                        {                           
                            defaults = "null";
                        }
                        CREATETABLE = GetTableStruct(CREATETABLE, fieldName, fieldType, allow_null, key, defaults, extra);
                    }

                }
                catch (System.Exception e)
                {
                    Util.WriteLog(e.ToString());
                }
            }
            CREATETABLE = CREATETABLE.Remove(CREATETABLE.LastIndexOf(',')) + ")ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;";
            using (var conn = GetMysqlConnect())
            {
                CreateTableData(conn,CREATETABLE);
            }
           
        }
        private const string SPACE = " ";
        private const string NOTNULL = "NOT NULL ";
        private const string DEFAULT = "DEFAULT ";
        private const string EXTRA = "AUTO_INCREMENT";
        private const string KEY = "PRIMARY KEY ";
        //private const string INDEX = "";
        private const string INDEX_FIRST = "create index IDX_";
        private const string INDEX_SECOND = "_";
        private const string INDEX_THIRD = " on ";
        private const string INSERT_FIRST = "insert into ";
        private const string INSERT_SECOND = ") values(";
        private const string UPDATE_FIRST = "update ";
        private const string UPDATE_SECOND = "set ";

        public const int UPDATE_DB_COUNT = 1;
        public const int COMBAT_LIST_MAXCOUNT = 50;
        //创建一个字段的字符串
        public static string GetTableStruct(string CREATETABLE, string field, string type, bool allow_null, string key, string defaults, string extra)
        {
            CREATETABLE = CREATETABLE + field + SPACE + type + SPACE;
            if (key == "PRI")
            {
                CREATETABLE = CREATETABLE + KEY;
                if (extra == "auto_increment")
                {
                    CREATETABLE = CREATETABLE + EXTRA + ",\n";
                }
                else
                {
                    CREATETABLE = CREATETABLE + ",\n";
                }
            }
            else if (key == "MUL" || key == string.Empty)
            {
                //if (key == "MUL")
                //{
                //    indexList.Add(field);
                //}
                if (!allow_null)
                    CREATETABLE = CREATETABLE + NOTNULL;

                if (!string.IsNullOrEmpty(defaults))
                {
                    CREATETABLE = CREATETABLE + DEFAULT + defaults + ",\n";
                }
                else
                {
                    CREATETABLE = CREATETABLE + ",\n";
                }
            }
            return CREATETABLE;
        }
        //动态执行sql
        public static void CreateTableData(MySqlConnection conn ,string cmdText)
        {
            MySqlParameter[] commandParameter = new MySqlParameter[]
                {
                    new MySqlParameter("pStr", cmdText)
                };
            MySqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "prCreateSQLData", commandParameter);
            
        }

        //动态执行sql
        public static void CreateSQLData(MySqlConnection conn, string cmdText)
        {
          
            MySqlParameter[] commandParameter = new MySqlParameter[0];
            MySqlHelper.ExecuteNonQuery(conn, CommandType.Text, cmdText, commandParameter);
          
        }
        //通过 C#类型 获得mysql数据库 类型
        public static string GetDBTypeByType(string tType)
        {
            //TINYINT 0false1true
            string dbType = string.Empty;
            switch (tType)
            {
                case "System.String":
                    dbType = "VARCHAR(128)";
                    break;
                case "System.Byte":
                    dbType = "TINYINT";
                    break;
                case "System.Int16":
                    dbType = "SMALLINT";
                    break;
                case "System.Int32":
                    dbType = "INT";
                    break;
                case "System.Single":
                    dbType = "FLOAT";
                    break;
                case "System.DateTime":
                    dbType = "DateTime";
                    break;
                case "System.Double":
                    dbType = "Double";
                    break;
                case "System.Boolean":
                    dbType = "TINYINT";
                    break;
                case "System.Single[]":
                    dbType = "VARCHAR(256)";
                    break;
                case "System.Int32[]":
                    dbType = "VARCHAR(512)";
                    break;
                case "System.String[]":
                    dbType = "VARCHAR(512)";
                    break;
                default:
                    //枚举类型
                    dbType = "VARCHAR(40)";
                    break;
                    //case "System.Collections.Generic.List`1[System.Int32]":
                    //    dbType = "VARCHAR(16)";
                    //    break;
            }
            return dbType;
        }
        //获得mysql连接
        public static MySqlConnection GetMysqlConnect()
        {
             MySqlConnection conn = new MySqlConnection(GetConText());
            return conn;
        }
        //从nlog 配置文件读取 数据库连接字符串
        public static string GetConText()
        {
            string connectionString = "";
            string file = "NLog.config";
            var xe = XElement.Load(file);
            var xns = xe.GetDefaultNamespace();
            var minlevelattr = xe.Descendants(xns + "targets").Elements(xns + "target").Elements(xns + "target").Elements(xns + "target").Attributes("connectionString").FirstOrDefault();
            if (minlevelattr != null)
            {
                connectionString = minlevelattr.Value;
            }
            return connectionString;

        }
        //从nlog 配置文件读取 数据库名
        public static string GetDBName()
        {
            string dbDatabase = "";
            string file = "NLog.config";
            var xe = XElement.Load(file);
            var xns = xe.GetDefaultNamespace();
            var minlevelattr = xe.Descendants(xns + "targets").Elements(xns + "target").Elements(xns + "target").Elements(xns + "target").Attributes("dbDatabase").FirstOrDefault();
            if (minlevelattr != null)
            {
                dbDatabase = minlevelattr.Value;
            }
           
            return dbDatabase;

        }
    }
}
